﻿Imports System.Data
Imports System.Data.SqlClient

Public Class CargasDAL

    Sub New()

    End Sub

    Function inserir(ByVal carValor As Double, ByVal tcCodigo As Integer,
                     ByVal segCodigo As Integer, ByVal protocolo As String, ByVal carStatus As Integer, ByVal carPeso As Double) As Integer
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim codigo As Integer
        Try

            'Comando para inserção de dados no banco
            cmd.CommandText = "INSERT INTO Cargas (car_valor, tc_codigo, seg_codigo," & _
                              " car_protocolo, car_status,car_peso)" & _
                              " VALUES (@carValor, @tcCodigo, @segCodigo, @protocolo,@carStatus,@carPeso)"

            'Parâmetros de entrada
            cmd.Parameters.Add(New SqlParameter("@carValor", SqlDbType.Decimal)).Value = carValor
            cmd.Parameters.Add(New SqlParameter("@tcCodigo", SqlDbType.Int)).Value = tcCodigo
            cmd.Parameters.Add(New SqlParameter("@segCodigo", SqlDbType.Int)).Value = segCodigo
            cmd.Parameters.Add(New SqlParameter("@protocolo", SqlDbType.VarChar)).Value = protocolo
            cmd.Parameters.Add(New SqlParameter("@carStatus", SqlDbType.Int)).Value = carStatus
            cmd.Parameters.Add(New SqlParameter("@carPeso", SqlDbType.Decimal)).Value = carPeso

            'Execução da inserção com retorno do valor da chave primária
            codigo = objD.executacomandoAI(cmd, "Cargas")

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return codigo
    End Function

    Sub alterar(ByVal codigo As Integer, ByVal carValor As Double, ByVal tcCodigo As Integer,
                     ByVal segCodigo As Integer, ByVal protocolo As String, ByVal carStatus As Integer, ByVal carPeso As Double)
        Dim cmd As New SqlCommand
        Dim objD As New Dados

        Try

            'Comando para atualização de dados no banco
            cmd.CommandText = "UPDATE Cargas SET car_valor = @carValor," & _
                " tc_codigo = @tcCodigo, seg_codigo = @segCodigo, car_protocolo=@protocolo , " & _
                "car_status=@carStatus,car_peso = @carPeso" & _
                " WHERE car_codigo = @codigo"

            'Parâmetros de entrada
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@carValor", SqlDbType.Decimal)).Value = carValor
            cmd.Parameters.Add(New SqlParameter("@tcCodigo", SqlDbType.Int)).Value = tcCodigo
            cmd.Parameters.Add(New SqlParameter("@segCodigo", SqlDbType.Int)).Value = segCodigo
            cmd.Parameters.Add(New SqlParameter("@carStatus", SqlDbType.Int)).Value = carStatus
            cmd.Parameters.Add(New SqlParameter("@protocolo", SqlDbType.VarChar)).Value = protocolo
            cmd.Parameters.Add(New SqlParameter("@carPeso", SqlDbType.Decimal)).Value = carPeso

            'Execução da alteração
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

    Sub excluir(ByVal codigo As Integer)
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Try
            'Comando para exclusão de um registro no banco
            cmd.CommandText = "DELETE FROM cargas WHERE car_codigo = @codigo"

            'Parâmetro de comparação
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo

            'Execução do comando
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

    Function localizarProtocolo(ByVal descricao As String) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            'Comando para localizar vários registros no banco
            cmd.CommandText = "SELECT * FROM vw_cad_cargas WHERE car_protocolo like @descricao"

            'Parâmetro de entrada
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" + descricao + "%"

            'Execução do comando
            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function

    'Function localizar(ByVal codigo As Integer) As DataTable
    '    Dim cmd As New SqlCommand
    '    Dim objD As New Dados
    '    Dim tab As DataTable = Nothing
    '    Try
    '        'Comando para localizar vários registros no banco
    '        cmd.CommandText = "SELECT * FROM cargas WHERE car_codigo = @codigo"

    '        'Parâmetro de entrada
    '        cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo

    '        'Execução do comando
    '        tab = objD.executaConsulta(cmd)

    '    Catch ex As Exception
    '        Throw ex
    '    End Try
    '    cmd = Nothing
    '    objD = Nothing
    '    Return tab
    '    tab = Nothing
    'End Function


    Function localizar(ByVal descricao As String) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            If (descricao = "ultimo") Then
                cmd.CommandText = "SELECT car_codigo = max(car_codigo) FROM cargas "
            ElseIf (descricao = "primeiro") Then
                cmd.CommandText = "SELECT car_codigo = min(car_codigo) FROM cargas "

            Else
                'Comando para localizar vários registros no banco
                Dim codigo = Integer.Parse(descricao)
                cmd.CommandText = "SELECT * FROM cargas WHERE car_codigo = @descricao"

                'Parâmetro de entrada
                cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descricao
            End If
            'Execução do comando
            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function

    Function localizarProxAnterior(ByVal descricao As String, ByVal codigo As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            If (descricao = "proximo") Then
                cmd.CommandText = "SELECT car_codigo FROM cargas WHERE car_codigo = (SELECT MIN(car_codigo) FROM cargas WHERE car_codigo > @codigo)"

            ElseIf (descricao = "anterior") Then
                cmd.CommandText = "SELECT car_codigo FROM cargas  WHERE car_codigo = (SELECT MAX(car_codigo) FROM cargas WHERE car_codigo < @codigo)"


            End If
            'Parâmetro de entrada
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            'Execução do comando
            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function

    Function VerificaExclusao(ByVal codigo As Integer) As Boolean
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim verifica As Boolean = False
        Try
            cmd.CommandText = "Select car_codigo from historico_carga where car_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            verifica = objD.executacomandoString(cmd, "historico_carga")
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return verifica
    End Function

    Function VerificaMovimentacao(ByVal codigo As Integer) As Boolean
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim verifica As Boolean = False
        Try
            cmd.CommandText = "Select car_codigo from cargas_do_agendamento where car_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            verifica = objD.executacomandoString(cmd, "cargas_do_agendamento")
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return verifica
    End Function
End Class

